# -*- coding: utf-8 -*-
# @File    : get_server_info.py
# @Date    : 2021-11-25
# @Author  : Richard

import os
import time

import pandas
import xlrd
from xlrd import xldate_as_tuple


# 解析Excel 类
class ExcelData(object):
    # 初始化方法
    def __init__(self, file, sh):
        self.file = file
        self.sh = sh
        self.data = xlrd.open_workbook(self.file)
        self.table = self.data.sheet_by_name(self.sh)
        self.keys = self.table.row_values(0)
        self.rowNum = self.table.nrows
        self.colNum = self.table.ncols

    # 定义一个读取excel表的方法
    def readExcel(self):
        datas = []
        for i in range(1, self.rowNum):
            sheet_data = {}
            for j in range(self.colNum):
                c_type = self.table.cell(i, j).ctype
                # 获取单元格数据
                c_cell = self.table.cell_value(i, j)
                if c_type == 2 and c_cell % 1 == 0:  # 如果是整形
                    c_cell = int(c_cell)
                elif c_type == 3:
                    # 转成datetime对象
                    date = pandas.datetime.datetime(*xldate_as_tuple(c_cell, 0))
                    c_cell = date.strftime('%Y/%d/%m %H:%M:%S')
                elif c_type == 4:
                    c_cell = True if c_cell == 1 else False
                sheet_data[self.keys[j]] = c_cell
            datas.append(sheet_data)
        return datas

    # 处理Excel
    def open_excel(self):
        """
        :param file_path:
        :return: Database IP address
        """
        data = []
        try:
            book = xlrd.open_workbook(self.file)
            res_obj = book.sheet_by_name("server_ip")
            for i in range(1, res_obj.nrows):
                data.append(res_obj.cell(i, 1))
            return data
        except Exception as Err:
            print("open excel error is :{0}".format(Err))
